SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.ARTYDelayedComp
(
    @FundId AS VARCHAR (10),
    @NAVDate AS SMALLDATETIME
) AS
BEGIN
SELECT  
		DISTINCT t.TradeId 'FS_TradeId' ,
		i.InstrName ,
		i.ExtId, 
		t.TradeDate,
		i.Spread,

		DATEADD(DAY, 10, t.TradeDate) AS 'Start_Delay_Comp',
		DATEDIFF(DAY, DATEADD(DAY , 10, t.TradeDate), t.SettlementDate) AS 'Delay_Comp_To_Settle', 
		CASE WHEN
			t.TradeType = 'Sale' THEN -1 * i.Spread * ABS(CAST(DATEDIFF(DAY, DATEADD(DAY , 10, t.TradeDate), t.SettlementDate) AS DECIMAL(18,5))) * t.TradeAmount / CAST(360 AS FLOAT)
			ELSE       i.Spread * ABS(CAST(DATEDIFF(DAY, DATEADD(DAY , 10, t.TradeDate), t.SettlementDate) AS DECIMAL(18,5))) * t.TradeAmount / CAST(360 AS FLOAT)
			END AS 'Accrued_Interest',
		t.SettlementDate,
		t.TradeAmount,
		t.TradeType ,
		t.PortfolioId
FROM    FSFundModel.Reporting.Asset a
        INNER JOIN FSFundModel.Reporting.Trade t ON t.ExportId = a.ExportId
                                                    AND t.InstrumentId = a.InstrumentId
                                                    AND t.PortfolioId = t.PortfolioId
                                                    AND t.LotId = a.LotId
        INNER JOIN FSFundModel.Reporting.Instrument i ON t.ExportId = i.ExportId
                                                         AND t.InstrumentId = i.InstrumentId
WHERE   t.ExportId = ( SELECT   MAX(e.ExportId)
                       FROM     FSFundModel.Reporting.Export e
                       WHERE    e.FundId = @FundId
					   AND e.NAVDate = @NAVDate
                     )
        AND t.IsSettled = 0 
		--AND LEFT(i.ExtId, 2) = 'LX'
		AND i.PricingVendor = 'Markit Loans'
		AND i.BaseRateType = 'LIBOR3Month'

		ORDER BY t.TradeId ASC
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Accounting', 'SCHEMA', N'dbo', 'PROCEDURE', N'ARTYDelayedComp', NULL, NULL
GO
